﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;
using System.Data;

namespace ZG_member.DAL
{
     public class Areas
    {
         /// <summary>
         /// 获取数据列表
         /// </summary>
         /// <param name="strWhere"></param>
         /// <returns></returns>
         public DataSet GetList(string strWhere)
         {
             
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select * ");
             strSql.Append(" FROM Areas ");
             if (strWhere.Trim() != "")
             {
                 strSql.Append(" where " + strWhere);
             }
             return DbHelperSQL.Query(strSql.ToString());
         }


         /// <summary>
         /// 是否存在该记录
         /// </summary>
         public bool Exists(int id, string AreaName)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select count(1) from Areas");
             strSql.Append(" where id=@id and AreaName = @AreaName");
             SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
                    new SqlParameter("@AreaName", SqlDbType.NVarChar,200)
};
             parameters[0].Value = id;
             parameters[1].Value = AreaName;

             return DbHelperSQL.Exists(strSql.ToString(), parameters);
         }
         /// <summary>
         /// 更新一条数据
         /// </summary>
         public bool Update(ZG_member.Model.Areas model)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("update Areas set ");
             strSql.Append("AreaName=@AreaName,");
             strSql.Append("AreaNumber=@AreaNumber");
             strSql.Append(" where id=@id");
             SqlParameter[] parameters = {
					new SqlParameter("@AreaName", SqlDbType.NVarChar,1000),
                    new SqlParameter("@AreaNumber", SqlDbType.NVarChar,1000),
					new SqlParameter("@id", SqlDbType.Int,4)};
             parameters[0].Value = model.AreaName;
             parameters[1].Value = model.AreaNumber;
             parameters[2].Value = model.id;

             int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }



         /// <summary>
         /// 增加一条数据
         /// </summary>
         public int Add(ZG_member.Model.Areas model)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("insert into Areas(");
             strSql.Append("AreaName,AreaNumber)");
             strSql.Append(" values (");
             strSql.Append("@AreaName,@AreaNumber)");
             strSql.Append(";select @@IDENTITY");
             SqlParameter[] parameters = {
					new SqlParameter("@AreaName", SqlDbType.NVarChar,1000),
                    new SqlParameter("@AreaNumber", SqlDbType.NVarChar,1000)
                                         };
             parameters[0].Value = model.AreaName;
             parameters[1].Value = model.AreaNumber;

             object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
             if (obj == null)
             {
                 return 0;
             }
             else
             {
                 return Convert.ToInt32(obj);
             }
         }

         /// <summary>
         /// 根据ID获得实体对象
         /// </summary>
         /// <param name="id"></param>
         /// <returns></returns>
         public ZG_member.Model.Areas GetModel(int id)
         {
             StringBuilder sb = new StringBuilder();
             sb.Append("select *from Areas");
             sb.Append(" where id=@id");
             SqlParameter[] sp ={
                new SqlParameter("@id",SqlDbType.Int,4)
                             };
             sp[0].Value = id;
             ZG_member.Model.Areas area = new ZG_member.Model.Areas();
             DataSet ds = DbHelperSQL.Query(sb.ToString(),sp);
             if (ds.Tables[0].Rows.Count > 0)
             {
                 if (ds.Tables[0].Rows[0]["id"] != "")
                 {
                     area.id =Convert.ToInt32(ds.Tables[0].Rows[0]["id"]);
                 }
                 if (ds.Tables[0].Rows[0]["AreaName"] != "")
                 {
                     area.AreaName = ds.Tables[0].Rows[0]["AreaName"].ToString(); ;
                 }
                 if (ds.Tables[0].Rows[0]["AreaNumber"] != "")
                 {
                     area.AreaNumber = ds.Tables[0].Rows[0]["AreaNumber"].ToString(); ;
                 }
             }
             return area;
         }
         /// <summary>
         /// 根据区域名称得到实体
         /// </summary>
         /// <param name="Name"></param>
         /// <returns></returns>
         public ZG_member.Model.Areas GetModelToString(string Name)
         {
             StringBuilder sb = new StringBuilder();
             sb.Append("select *from Areas");
             sb.Append(" where AreaName like '"+Name+"%'");

             ZG_member.Model.Areas area = new ZG_member.Model.Areas();
             DataSet ds = DbHelperSQL.Query(sb.ToString());
             if (ds.Tables[0].Rows.Count > 0)
             {
                 if (ds.Tables[0].Rows[0]["id"] != "")
                 {
                     area.id = Convert.ToInt32(ds.Tables[0].Rows[0]["id"]);
                 }
                 if (ds.Tables[0].Rows[0]["AreaName"] != "")
                 {
                     area.AreaName = ds.Tables[0].Rows[0]["AreaName"].ToString(); ;
                 }
                 if (ds.Tables[0].Rows[0]["AreaNumber"] != "")
                 {
                     area.AreaNumber = ds.Tables[0].Rows[0]["AreaNumber"].ToString(); ;
                 }
             }
             return area;
         }

    }
}
